# -*- codeing=utf-8 -*-
# @Time : 2021/1/14 23:18
# @Author : 李思行
# @File : douban.py
# @Software : PyCharm

import re
from bs4 import BeautifulSoup
import urllib.request,urllib.error
import xlwt
import sqlite3

findLink=re.compile(r'<a href="(.*?)">')
findImage=re.compile(r'<img.*class="" src="(.*?)" width="100"/>',re.S)
findTitle=re.compile(r'<span class="title">(.*)</span>')
findBd=re.compile(r'<p class="">(.*?)</p>',re.S)
findRating=re.compile(r'<span class="rating_num" property="v:average">(.*)</span>')
findNum=re.compile(r'<span>(\d*)人评价</span>')
findInq=re.compile(r'<span class="inq">(.*)</span>')

#主函数
def main():
    baseurl="https://movie.douban.com/top250?start="
    #获取数据
    datalist=getData(baseurl)
    #保存数据
    savaData(datalist)
    #保存数据到数据库
    dbpath="movie.db"
    savaDataDB(datalist,dbpath)
#爬取数据
def getData(baseurl):
    datalist=[]
    for i in range(0, 10):
        url = baseurl + str(i * 25)
        html=askData(url)
        soup=BeautifulSoup(html,"html.parser")
        for item in soup.find_all('div',class_="item"):
            data=[]
            item=str(item)
            link=re.findall(findLink,item)[0]
            data.append(link)
            image=re.findall(findImage,item)[0]
            data.append(image)
            title=re.findall(findTitle,item)
            if len(title)==2:
                ctitle=title[0]
                data.append(ctitle)
                otitle=' '.join(title[1].replace('/','').split())
                data.append(otitle)
            else:
                data.append(title[0])
                data.append(" ")
            bd=re.findall(findBd,item)[0]
            bd=re.sub('<br(\s+)?/>(\s+)?','',bd)
            bd=' '.join(bd.replace('/','').split())
            data.append(bd)
            rating=re.findall(findRating,item)[0]
            data.append(rating)
            judgenum=re.findall(findNum,item)[0]
            data.append(judgenum)
            inq=re.findall(findInq,item)
            if  len(inq)!=0:
                inq=inq[0].replace(".","")
                data.append(inq)
            else:
                data.append(" ")
            datalist.append(data)

    return datalist
    #解析数据

#获取网站
def askData(url):
    header={"User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4044.138 Safari/537.36"}
    req=urllib.request.Request(url,headers=header)
    html=""
    try:
        response=urllib.request.urlopen(req)
        html=response.read().decode("utf-8")
    except Exception as e:
        if hasattr(e,"code"):
            print(e.code)
        if hasattr(e,"reason"):
            print(e.reason)

    return html

#保存数据
def savaData(datalist):
    workbook=xlwt.Workbook(encoding='utf-8')
    worksheet=workbook.add_sheet('豆瓣电影TOP250--练习1.xls')
    col=("电影详情链接","电影海报链接","电影中文名","电影外文名","演员表","电影评分","评分人数","电影结语")
    for i in range(0,8):
        worksheet.write(0,i,col[i])
    for i in range(0,250):
        print("第%s条"%i)
        data=datalist[i]
        for j in range(0,8):
            worksheet.write(i+1,j,data[j])
    workbook.save('豆瓣电影TOP250--练习1.xls')

#保存数据到数据库
def savaDataDB(datalist,dbpath):
    init_db(dbpath)
    conn=sqlite3.connect(dbpath)
    cur=conn.cursor()

    for data    in datalist:
        for index   in range(len(data)):
            if  index!=5 or index!=6:
                data[index]='"'+data[index]+'"'
        sql='''
                insert  into    movie(info_link,pic_link,cname,ename,info,score,rated,instroduction)
                values(%s)
        '''%",".join(data)
        cur.execute(sql)
        conn.commit()
    conn.close()

#创建一个数据库,一个表
def init_db(dbpath):
    sql='''
        create  table   movie
        (
            id integer primary key autoincrement,
            info_link   text,
            pic_link    text,
            cname       varchar ,
            ename       varchar ,
            info        text,
            score       numeric,
            rated       numeric,
            instroduction text
        )
    '''
    conn=sqlite3.connect(dbpath)
    cursor=conn.cursor()
    cursor.execute(sql)
    conn.commit()
    conn.close()

if __name__=="__main__":
    main()